使用 WITH RECURSIVE 生成连续日期

可以使用 WITH RECURSIVE 语法来创建递归查询，生成连续日期或执行其他递归操作。下面是示例代码：

• mysql

WITH RECURSIVE date_series AS (
  SELECT DATE('2023-01-01') AS date -- 起始日期
  UNION ALL
  SELECT DATE_ADD(date, INTERVAL 1 DAY)
  FROM date_series
  WHERE date <= '2023-01-31' -- 结束日期
)
SELECT date
FROM date_series;

• sql server

WITH date_series AS (
  SELECT CAST('2023-01-01' AS DATE) AS date -- 起始日期
  UNION ALL
  SELECT DATEADD(DAY, 1, date)
  FROM date_series
  WHERE date < '2023-01-31' -- 结束日期
)
SELECT date
FROM date_series;

• postgresql

WITH RECURSIVE date_series AS (
  SELECT
    '2023-01-01'::date AS date -- 起始日期
  UNION ALL
  SELECT
    date + 1
  FROM
    date_series
  WHERE
    date <= '2023-01-31'::date -- 结束日期
)
SELECT date
FROM date_series;